package sim.core.dbrecord;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import sim.core.consts.CompareEnum;
import sim.core.consts.OperatorEnum;
import sim.core.consts.OrderByEnum;
import sim.core.consts.StatementEnum;
import sim.core.utils.StringUtils;

/**
 * SQL 语句生成类
 * 
 * @author Feny
 * @email 1220301855@qq.com
 * @version 1.0
 * @date 2018年6月4日上午11:12:55
 */
public class SqlBuilder {
	private static String SELECT = "*";
	private static String Statement_Type;// SQL 语句类型
	private static String TABLE;// 表名
	private static List<Map<String, Object>> WHERE = new ArrayList<>();

	private static List<Object> sets = new ArrayList<>();
	private static List<String> columns = new ArrayList<>();
	private static List<Object> values = new ArrayList<>();

	private static List<String> orderBy = new ArrayList<>();
	private static List<String> groupBy = new ArrayList<>();

	private static String leftOuterJoin = null;
	private static String rightOuterJoin = null;
	private static String innerJoin = null;
	private static String outerJoin = null;

	public static String getSQL() {
		StringBuilder sql = null;
		if (StringUtils.equals(Statement_Type, StatementEnum.SELECT.name())) {
			sql = new StringBuilder(format("SELECT %s FROM %s", SELECT, TABLE));
		} else if (StringUtils.equals(Statement_Type, StatementEnum.UPDATE.name())) {
			sql = new StringBuilder(format("UPDATE %s SET ", TABLE));
			SETS(sql);
		} else if (StringUtils.equals(Statement_Type, StatementEnum.DELETE.name())) {
			sql = new StringBuilder(format("DELETE FROM %s", TABLE));
		} else if (StringUtils.equals(Statement_Type, StatementEnum.INSERT.name())) {
			sql = new StringBuilder(format("INSERT INTO %s ", TABLE));
			INTO_VALUES(sql);
		}
		sql.append(StringUtils.isNotBlank(leftOuterJoin) ? leftOuterJoin : "");
		sql.append(StringUtils.isNotBlank(rightOuterJoin) ? rightOuterJoin : "");
		sql.append(StringUtils.isNotBlank(innerJoin) ? innerJoin : "");
		sql.append(StringUtils.isNotBlank(outerJoin) ? outerJoin : "");

		if (null != WHERE && WHERE.size() >= 0 && !StringUtils.equals(Statement_Type, StatementEnum.INSERT.name())) {
			sql.append(getWhereSql());
		}
		getGroupBy(sql);
		getOrderBy(sql);
		return sql.toString();
	}

	public static void begin() {
		reset();
	}

	public static void reset() {
		WHERE.clear();
		sets.clear();
		columns.clear();
		values.clear();
		leftOuterJoin = null;
		rightOuterJoin = null;
		innerJoin = null;
		outerJoin = null;
	}

	public static Object[] getParams() {
		try {
			return values.toArray();
		} finally {
			reset();
		}
	}

	public static void select(String select) {
		if (StringUtils.isNotBlank(select)) {
			SELECT = select;
		}
	}

	public static void insert(String table) {
		if (StringUtils.isNotBlank(table)) {
			setTable(table, StatementEnum.INSERT);
		}
	}

	public static void update(String table) {
		if (StringUtils.isNotBlank(table)) {
			setTable(table, StatementEnum.UPDATE);
		}
	}

	public static void delete(String table) {
		if (StringUtils.isNotBlank(table)) {
			setTable(table, StatementEnum.DELETE);
		}
	}

	public static void from(String table) {
		if (StringUtils.isNotBlank(table)) {
			setTable(table, StatementEnum.SELECT);
		}
	}

	public static void where(String column) {
		where(column, null, null);
	}

	public static void where(String column, Object value) {
		where(column, value, null);
	}

	public static void where(String column, Object value, CompareEnum compare) {
		where(column, value, compare, null);
	}

	public static void where(String column, Object value, CompareEnum compare, OperatorEnum operator) {
		Boolean flag = null == value || (value instanceof String && StringUtils.isBlank((String) value));
		value = flag ? null : value;
		Map<String, Object> arg = new HashMap<>();
		String c = null != compare ? compare.getVal() : CompareEnum.EQ.getVal();
		String o = (null != operator ? operator.name() : OperatorEnum.AND.name());
		if (flag) {
			// 拼接结果：column [=, >, <, >=, <=, <>] ? [AND,OR]
			column = String.format(" %s %s", column, o);
		} else {
			if (StringUtils.equals(CompareEnum.LIKE.getVal(), c)) {
				// 拼接结果：column LIKE CONCAT('%',?,'%') [AND,OR]
				column = String.format(" %s %s %s %s", column, c, "CONCAT('%',?,'%')", o);
			} else if (StringUtils.equals(CompareEnum.BETWEEN.getVal(), c)) {
				if (!value.getClass().isArray()) {
					throw new IllegalArgumentException("BETWEEN AND查询值类型为Object[]");
				}
				if (((Object[]) value).length != 2) {
					throw new IllegalArgumentException("BETWEEN AND查询值个数必须为2个");
				}
				// 拼接结果：column BETWEEN ? AND ? [AND,OR]
				column = String.format(" %s %s ? %s ? %s", column, c, OperatorEnum.AND.name(), o);
			} else if (StringUtils.equals(CompareEnum.IN.name(), c) || StringUtils.equals(CompareEnum.NOT_IN.getVal(), c)) {
				List<Object> val;
				if (value.getClass().isArray()) {
					val = Arrays.asList((Object[]) value);
				} else {
					val = Arrays.asList(value);
				}
				// 拼接结果：column [NOT IN,IN]([根据参数个数生成?]) [AND,OR]
				column = String.format(" %s %s (%s) %s", column, c, generate(val.size()), o);
			} else {
				// 拼接结果：column [NOT IN,IN](?) [AND,OR]
				column = String.format(" %s %s ? %s", column, c, o);
			}
		}

		arg.put(column, value);
		WHERE.add(arg);
	}

	public static void sets(String column, Object value) {
		values(column, value);
	}

	private static void SETS(StringBuilder sb) {
		for (String column : columns) {
			sb.append(format("%s=?,", column));
		}
		sb.deleteCharAt(sb.length() - 1);
	}

	public static void values(String column, Object value) {
		columns.add(column);
		values.add(value);
	}

	public static void leftOuterJoin(String table, String join) {
		leftOuterJoin = format(" LEFT OUTER JOIN %s ON %s", table, join);
	}

	public static void rightOuterJoin(String table, String join) {
		rightOuterJoin = format(" RIGHT OUTER JOIN %s ON %s", table, join);
	}

	public static void innerJoin(String table, String join) {
		innerJoin = format(" INNER JOIN %s ON %s", table, join);
	}

	public static void outerJoin(String table, String join) {
		outerJoin = format(" OUTER JOIN %s ON %s", table, join);
	}

	public static void groupBy(String column) {
		groupBy.add(column);
	}

	private static void getGroupBy(StringBuilder sb) {
		if (null != groupBy && groupBy.size() > 0) {
			sb.append(format(" GROUP BY %s", String.join(",", groupBy)));
		}
	}

	public static void orderBy(String column) {
		orderBy.add(column);
	}

	public static void orderBy(String column, OrderByEnum by) {
		orderBy.add(format("%s %s", column, by));
	}

	private static void getOrderBy(StringBuilder sb) {
		if (null != orderBy && orderBy.size() > 0) {
			sb.append(format(" ORDER BY %s", String.join(",", orderBy)));
		}
	}

	private static void setTable(String table, StatementEnum type) {
		if (StringUtils.isNotBlank(table)) {
			TABLE = table;
			Statement_Type = type.name();
		}
	}

	private static void INTO_VALUES(StringBuilder sb) {
		sb.append(format("(%s)", String.join(",", columns)));
		sb.append(format(" VALUES(%s)", generate(columns.size())));
	}

	private static String getWhereSql() {
		if (WHERE.size() == 0) {
			return "";
		}
		StringBuilder where = new StringBuilder(30);
		where.append(" WHERE");
		for (Map<String, Object> map : WHERE) {
			for (String key : map.keySet()) {
				where.append(key);
				if (null != map.get(key)) {
					if (map.get(key).getClass().isArray()) {
						values.addAll(Arrays.asList((Object[]) map.get(key)));
					} else {
						values.add(map.get(key));
					}
				}
			}
		}
		int len = where.length();
		where.delete(len - 3, len);// 删除最后的AND或OR
		return where.toString();
	}

	private static String format(String format, Object... args) {
		return String.format(format, args);
	}

	/**
	 * 生成多个?
	 * 
	 * @author Feny
	 * @date 2018年6月8日上午10:42:01
	 * @return
	 */
	private static String generate(int size) {
		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < size; i++) {
			sb.append("?,");
		}
		sb.deleteCharAt(sb.length() - 1);
		return sb.toString();
	}
}
